﻿
SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_GetStaffInfoEx]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_GetStaffInfoEx];
GO
CREATE PROCEDURE [dbo].[sproc_GetStaffInfoEx] 
    @xml xml
AS
BEGIN
SET NOCOUNT ON

/*
 * XML format:
 * <staffs>
 *  <s id="" />
 * </staffs>
 */

select
	a.*,
    datediff(yy, a.birthday, getdate()) as Age,
	(case a.sex when 1 then N'男' else N'女' end) as SexName,
	convert(nvarchar(10),a.RegistedDate,120) AS RQ,b.position_id AS position_id,
	(SELECT Position_name FROM uds_Position c WHERE c.Position_id = b.Position_id) AS Position_Name 
from
	dbo.uds_staff a,
	dbo.uds_staff_in_position b,
	@xml.nodes('/staffs/s') R(s)
WHERE 
	a.Staff_ID = b.Staff_ID
	and a.Staff_ID = R.s.value('@id', 'int');

END
GO